﻿using System;
using System.Data;
using System.Collections.Generic;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for SLATable
    /// </summary>
    public class SLATable : CacheAbstraction
    {
        public DataTable GetSLA(String sxCustomerId, String sxCARId,
            int iStartMonth, int iStartYear, int iEndMonth, int iEndYear)
        {
            return GetSLA(sxCustomerId, sxCARId, iStartMonth, iStartYear, iEndMonth, iEndYear, false);
        }
        public DataTable GetSLA(String sxCustomerId, String sxCARId,
            int iStartMonth, int iStartYear, int iEndMonth, int iEndYear, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}{4}_{5}{6}",
                KCache.T_SLA,
                sxCustomerId.ToString(),
                sxCARId.ToString(),
                iStartMonth.ToString(),
                iStartYear.ToString(),
                iEndMonth.ToString(),
                iEndYear.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                String sxExtraParam = String.Empty;
                List<OracleParameter> lsParams = new List<OracleParameter>();
                //=================Ini mesti dibalik logikanya
                if (!String.IsNullOrEmpty(sxCustomerId) && !String.IsNullOrEmpty(sxCARId))
                {
                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmCustomerId";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = sxCustomerId;

                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmCARId";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = sxCARId;

                    sxExtraParam = "where v2.COMPANY_ID = :prmCustomerId";
                    sxExtraParam +=
                        @" and v2.EMPLOYEE_NO IN
                                (
									select :prmCARId from dual
                                    UNION ALL
                                    SELECT DISTINCT NIK
                                    FROM 
                                    v_icp_escalation
                                    START WITH NIK = :prmCARId        
                                    CONNECT BY PRIOR t_qrtz_user_destination_id = parent_id
                                )
                        ";
                }
                else if (!String.IsNullOrEmpty(sxCARId))
                {
                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmCARId";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = sxCARId;

                    sxExtraParam = 
                            @"where v2.EMPLOYEE_NO IN
                                (
									select :prmCARId from dual
                                    UNION ALL
                                    SELECT DISTINCT NIK
                                    FROM 
                                    v_icp_escalation
                                    START WITH NIK = :prmCARId        
                                    CONNECT BY PRIOR t_qrtz_user_destination_id = parent_id
                                )
                            ";
                }
                //=================================
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iStartMonth;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iEndMonth;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iStartYear;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iEndYear;
                if (!String.IsNullOrEmpty(sxExtraParam))
                {
                    sxExtraParam += " and ";
                }
                else
                {
                    sxExtraParam += " where ";
                }
                //sxExtraParam +=
                //    "((MONTH >= :prmStartMonth and YEAR = :prmStartYear)"+
                //    "or (YEAR > :prmStartYear and YEAR < :prmEndYear)"+
                //    "or (MONTH <= :prmEndMonth and YEAR = :prmEndYear))";
                sxExtraParam +=
                   @"
                    (
                        to_date('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') < ADD_MONTHS(to_date('1\' || (MONTH) || '\' || (YEAR), 'dd\mm\yyyy'), 1)
                        and
                        ADD_MONTHS(to_date('1\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy'), 1) >= ADD_MONTHS(to_date('1\' || (MONTH) || '\' || (YEAR), 'dd\mm\yyyy'), 1)
                    )
                    ";
                String sxQuery = String.Format(
                    @"
                    select
                        v1.MONTH,
                        v1.YEAR,
                        v1.DELIVERED_DATE,
                        v1.T_FILE_UPLOAD_ID,
                        v1.ORIGINAL_FILENAME,
                        v1.CONTACT_NAME,
                        v1.COMPANY_NAME,
                        v2.COMPANY_ID CUSTOMER_ID,
                        v2.EMPLOYEE_NO CAR_ID,
                        v2.EMPLOYEE_NAME CAR,
                        'MANUAL' TYPE
                    from 
                        V_ICP_SLA_LIST v1
                    left join
                        (
                        select
                            COMPANY_ID,
                            EMPLOYEE_NAME,
                            EMPLOYEE_NO
                        from
                            V_ICP_CAR
                        )v2
                    on
                        v1.CUSTOMER_ID = v2.COMPANY_ID
                    {0}
                    order by
                    v1.COMPANY_NAME asc
                    ",
                     sxExtraParam);

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
                DataTable dt2 = GetSLA_OLAP(sxCustomerId, sxCARId,
                    iStartMonth, iStartYear, iEndMonth, iEndYear, bForceRefresh);

                dt2.Merge(dt.Copy());

                AddToCache(sxCacheKey, dt2, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }


        public DataTable GetSLA_OLAP(String sxCustomerId, String sxCARId,
            int iStartMonth, int iStartYear, int iEndMonth, int iEndYear)
        {
            return GetSLA_OLAP(sxCustomerId, sxCARId, iStartMonth, iStartYear, iEndMonth, iEndYear, false);
        }
        public DataTable GetSLA_OLAP(String sxCustomerId, String sxCARId,
            int iStartMonth, int iStartYear, int iEndMonth, int iEndYear, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}{4}_{5}{6}",
                KCache.T_OLAP_SLA_CAR,
                sxCustomerId,
                sxCARId,
                iStartMonth.ToString(),
                iStartYear.ToString(),
                iEndMonth.ToString(),
                iEndYear.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.WEB_USER);
                String sxExtraParam = String.Empty;
                List<OracleParameter> lsParams = new List<OracleParameter>();
                if (!String.IsNullOrEmpty(sxCustomerId) && !String.IsNullOrEmpty(sxCARId))
                {
                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmCustomerId";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = sxCustomerId;

                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmCARId";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = sxCARId;

                    sxExtraParam = "where CUSTOMER_ID = :prmCustomerId";
                    sxExtraParam +=
                        @" and CAR_ID IN
                                (
                                    select :prmCARId from dual
                                    UNION ALL
                                    SELECT distinct NIK
                                    FROM   
                                        sni.t_qrtz_user_destination
                                    WHERE
                                        NVL (VALID_TO, SYSDATE + 1) > SYSDATE 
                                    AND 
                                        ESCALATION_LEVEL < 5
                                    START WITH NIK = :prmCARId        
                                    CONNECT BY PRIOR t_qrtz_user_destination_id = parent_id
                                )
                        ";
                }
                else if (!String.IsNullOrEmpty(sxCARId))
                {
                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmCARId";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = sxCARId;

                    sxExtraParam =
                            @"where CAR_ID IN
                                (
                                    select :prmCARId from dual
                                    UNION ALL
                                    SELECT distinct NIK
                                    FROM   
                                        sni.t_qrtz_user_destination
                                    WHERE
                                        NVL (VALID_TO, SYSDATE + 1) > SYSDATE 
                                    AND 
                                        ESCALATION_LEVEL < 5
                                    START WITH NIK = :prmCARId        
                                    CONNECT BY PRIOR t_qrtz_user_destination_id = parent_id
                                )
                            ";
                }
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iStartMonth;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iEndMonth;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iStartYear;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iEndYear;
                if (!String.IsNullOrEmpty(sxExtraParam))
                {
                    sxExtraParam += " and ";
                }
                else
                {
                    sxExtraParam += " where ";
                }
                //sxExtraParam +=
                //    "((MONTH >= :prmStartMonth and YEAR = :prmStartYear)"+
                //    "or (YEAR > :prmStartYear and YEAR < :prmEndYear)"+
                //    "or (MONTH <= :prmEndMonth and YEAR = :prmEndYear))";
                sxExtraParam +=
                   @"
                    (
                        to_date('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') < ADD_MONTHS(to_date('1\' || (MONTH) || '\' || (YEAR), 'dd\mm\yyyy'), 1)
                        and
                        ADD_MONTHS(to_date('1\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy'), 1) >= ADD_MONTHS(to_date('1\' || (MONTH) || '\' || (YEAR), 'dd\mm\yyyy'), 1)
                    )
                    ";

                String sxQuery = String.Format(
                    @"
                    select * from
                    (
                        select 
                            CAST(SUBSTR(PERIODE_CAR,5,2) as number) MONTH,
                            CAST(SUBSTR(PERIODE_CAR,1,4) as number) YEAR,
                            SEND_MAIL_DATE DELIVERED_DATE,
                            0 T_FILE_UPLOAD_ID,
                            OUTPUT_FILE_NAME ORIGINAL_FILENAME,
                            '-' CONTACT_NAME,
                            COMPANY_NAME,
                            COMPANY_ID CUSTOMER_ID,
                            EMPLOYEE_NO CAR_ID,
                            EMPLOYEE_NAME CAR,
                            'AUTO' TYPE
                        from
                            T_OLAP_SLA_CAR
                            union all
                            select 
                                CAST(SUBSTR(PERIODE_CAR,5,2) as number) MONTH,
                                CAST(SUBSTR(PERIODE_CAR,1,4) as number) YEAR,
                                SEND_MAIL_DATE DELIVERED_DATE,
                                1 T_FILE_UPLOAD_ID,
                                OUTPUT_FILE_NAME ORIGINAL_FILENAME,
                                '-' CONTACT_NAME,
                                COMPANY_NAME,
                                COMPANY_ID CUSTOMER_ID,
                                EMPLOYEE_NO CAR_ID,
                                EMPLOYEE_NAME CAR,
                                'AUTO' TYPE
                            from
                                T_OLAP_SLA_CAR_LOG
                    )
                    {0}
                    order by
                    COMPANY_NAME asc
                    ",
                     sxExtraParam);

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public override String GetCacheFilename()
        {
            return KCache.Filename.SLA;
        }
    }
}